﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using DataAccessLayer.Models.POJO;
using DataAccessLayer.Mapper;
using PetaPoco;
using MySql.Data.MySqlClient;


namespace DataAccessLayer.Service
{
    public class userInfoService : userInfoMapper
    {
        public static MySqlConnection connection = null;

        public userInfoService()
        {
            
        }
        public void create()
        {
            connection = new MySqlConnection("data source = localhost; database = dotnet; user id = root; password = 123456; pooling = true; charset = utf8; ");
        }
        public void Open()
        {
            connection.Open();
        }

        public void Close()
        {
            connection.Close();
        }



        public bool Delete(userInfoPeta userInfo)
        {
            create();
            var db = new PetaPoco.Database(connection);
            Open();
            if (db.Delete<userInfoPeta>("where userId = @0", userInfo.userId) > 0)
            {
                Close();
                return true;
            }
            else
            {
                Close();
                return false;
            }

        }

        public bool Insert(userInfoPeta userInfo)
        {
            try
            {
                create();
                var db = new PetaPoco.Database(connection);
                Open();
                db.Insert(userInfo);
                Close();
            }
            catch(Exception ex)
            {
                Close();
                return false;
            }
 
            return true;
        }

        public List<userInfoPeta> SelectAllUserInfo()
        {
            create();
            var db = new PetaPoco.Database(connection);
            Open();
            List<userInfoPeta>list = db.Query<userInfoPeta>("select * from userinfo03").ToList();
            Close();
            return list;
        }

        public userInfoPeta SelectSingleUserInfoPetaById(string userId)
        {
            create();
            var db = new PetaPoco.Database(connection);
            Open();
            String sql = "select * from userinfo03 where userId = '{0}'";
            sql = String.Format(sql,userId);
            List<userInfoPeta> list = db.Query<userInfoPeta>(sql).ToList();
            Close();
            if (list.Count != 0)
                return list[0];
            else return null;
        }



        public bool UpdatePassword(userInfoPeta userInfo)
        {
            create();
            var db = new PetaPoco.Database(connection);
            Open();
            if (db.Update<userInfoPeta>("set password = @0 where userId = @1", userInfo.password, userInfo.userId) > 0)
            {
                Close();
                return true;
            }
            else
            {
                Close();
                return false;
            }
        }

        public bool UpdateUserInfo(userInfoPeta userInfo)
        {
            create();
            var db = new PetaPoco.Database(connection);
            Open();
            if (db.Update<userInfoPeta>("set password = @0 , userName = @1 where userId = @2", userInfo.password, userInfo.userName,userInfo.userId) > 0)
            {
                Close();
                return true;
            }
            else
            {
                Close();
                return false;
            }
        }
    }
}
